﻿using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.OracleClient;
using System.Text;

namespace QT.DBO
{
    public class OraDBHelper
    {
        //########################## web.Config 的ORACLEDB连接写法  ##############################################
        //<connectionStrings>
        //    <add name="DB_ConnectionString" connectionString="Data Source=CHG;User ID=USERID;Password=PASSWORD;" providerName="System.Data.OracleClient"/>
        //</connectionStrings>
        //#####################################################################################################

        private string _dsn = ConfigurationManager.ConnectionStrings["DB_ConnectionString"].ConnectionString;

        public OraDBHelper()
        {
            Disponse(false);
        }

        public OraDBHelper(string dsn)
        {
            _dsn = dsn;
        }

        protected virtual void Disponse(bool disponsing)
        {
            if (disponsing)
            {

            }
        }

        #region ExecuteReader

        /// <summary>
        /// 执行一句SQL语句，返回DataReader
        /// </summary>
        /// <param name="Sqlstr">SQL语句</param>
        /// <returns>OleDbDataReader</returns>
        public OracleDataReader ExecuteReader(string Sqlstr)
        {
            OracleCommand cmd = new OracleCommand();

            OracleConnection cn = new OracleConnection(_dsn);

            try
            {
                PrepareCommand(cn, cmd, null, CommandType.Text, Sqlstr, null);
                OracleDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                return dr;
            }
            catch (OracleException ex)
            {
                cn.Close();
                throw new Exception(ex.Message);
            }
        }

        /// <summary>
        /// 执行一句带参数的SQL语句，返回DataReader
        /// </summary>
        /// <param name="Sqlstr">SQL语句</param>
        /// <param name="parms">参数</param>
        /// <returns>OleDbDataReader</returns>
        public OracleDataReader ExecuteReader(string Sqlstr, OracleParameter[] parms)
        {
            OracleCommand cmd = new OracleCommand();

            OracleConnection cn = new OracleConnection(_dsn);

            try
            {
                PrepareCommand(cn, cmd, null, CommandType.Text, Sqlstr, parms);
                OracleDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                cmd.Parameters.Clear();
                return dr;
            }
            catch (OracleException ex)
            {
                cn.Close();
                throw new Exception(ex.Message);
            }
        }
        #endregion

        #region DataTable

        /// <summary>
        /// 执行一句SQL语句，返回DataTable
        /// </summary>
        /// <param name="Sqlstr">SQL语句</param>
        /// <returns>DataTable</returns>
        public DataTable DataTable(string Sqlstr)
        {
            DataTable dt = new DataTable();

            OracleCommand cmd = new OracleCommand();

            try
            {
                using (OracleConnection cn = new OracleConnection(_dsn))
                {
                    PrepareCommand(cn, cmd, null, CommandType.Text, Sqlstr, null);
                    OracleDataAdapter da = new OracleDataAdapter();
                    da.SelectCommand = cmd;
                    da.Fill(dt);
                    //    using (OracleDataReader da = new OracleDataReader(Sqlstr, cn))
                    //    {
                    //        da.Fill(dt);
                    //    }
                }
            }
            catch (OracleException ex)
            {
                throw new Exception(ex.Message);
            }
            return dt;
        }

        /// <summary>
        /// 执行一句带参数的SQL语句，返回DataTable
        /// </summary>
        /// <param name="Sqlstr">SQL语句</param>
        /// <param name="parms">参数</param>
        /// <returns>DataTable</returns>
        public DataTable DataTable(string Sqlstr, OracleParameter[] parms)
        {
            DataTable dt = new DataTable();

            OracleCommand cmd = new OracleCommand();

            try
            {
                using (OracleConnection cn = new OracleConnection(_dsn))
                {
                    PrepareCommand(cn, cmd, null, CommandType.Text, Sqlstr, parms);
                    OracleDataAdapter da = new OracleDataAdapter();
                    da.SelectCommand = cmd;
                    da.Fill(dt);
                }
            }
            catch (OracleException ex)
            {
                throw new Exception(ex.Message);
            }
            finally
            {
                cmd = null;
            }
            return dt;
        }
        #endregion

        #region DataSet

        /// <summary>
        /// 执行一句SQL语句，返回DataSet
        /// </summary>
        /// <param name="Sqlstr">SQL语句</param>
        /// <returns>DataSet</returns>
        public DataSet DataSet(string Sqlstr)
        {
            DataSet ds = null;

            try
            {
                using (OracleConnection cn = new OracleConnection(_dsn))
                {
                    using (OracleDataAdapter da = new OracleDataAdapter(Sqlstr, cn))
                    {
                        da.Fill(ds);
                    }
                }
            }
            catch (OracleException ex)
            {
                throw new Exception(ex.Message);
            }
            return ds;

        }

        /// <summary>
        /// 执行一句带参数的SQL语句，返回DataSet
        /// </summary>
        /// <param name="Sqlstr">SQL语句</param>
        /// <param name="parms">参数</param>
        /// <returns>DataSet</returns>
        public DataSet DataSet(string Sqlstr, OracleParameter[] parms)
        {
            DataSet ds = null;

            OracleCommand cmd = new OracleCommand();

            try
            {
                using (OracleConnection cn = new OracleConnection(_dsn))
                {
                    PrepareCommand(cn, cmd, null, CommandType.Text, Sqlstr, parms);
                    OracleDataAdapter da = new OracleDataAdapter();
                    da.Fill(ds);
                }
            }
            catch (OracleException ex)
            {
                throw new Exception(ex.Message);
            }
            finally
            {
                cmd = null;
            }
            return ds;

        }
        #endregion

        #region ExecuteNonQuery

        /// <summary>
        /// 执行一句SQL语句，返回bool
        /// </summary>
        /// <param name="Sqlstr">SQL语句</param>
        /// <returns>Int</returns>
        public int ExecuteNonQuery(string Sqlstr)
        {
            OracleCommand cmd = new OracleCommand();

            using (OracleConnection cn = new OracleConnection(_dsn))
            {
                PrepareCommand(cn, cmd, null, CommandType.Text, Sqlstr, null);
                try
                {
                    int val = cmd.ExecuteNonQuery();
                    return val;
                }
                catch
                {
                    return -1;
                }
            }
        }

        /// <summary>
        /// 执行一句但参数的SQL语句，返回bool
        /// </summary>
        /// <param name="Sqlstr">SQL语句</param>
        /// <param name="parms">参数</param>
        /// <returns>Int</returns>
        public int ExecuteNonQuery(string Sqlstr, OracleParameter[] parms)
        {
            OracleCommand cmd = new OracleCommand();

            using (OracleConnection cn = new OracleConnection(_dsn))
            {
                PrepareCommand(cn, cmd, null, CommandType.Text, Sqlstr, parms);
                try
                {
                    int val = cmd.ExecuteNonQuery();
                    return val;
                }
                catch
                {
                    return -1;
                }
            }
        }

        #endregion

        #region ExecuteScalar

        /// <summary>
        /// 读取一个值 返回Object对象
        /// </summary>
        /// <param name="Sqlstr">SQL语句</param>
        /// <param name="parms">参数</param>
        /// <returns>返回Object对象</returns>
        public object ExecuteScalar(string Sqlstr, OracleParameter[] parms)
        {

            OracleCommand cmd = new OracleCommand();
            using (OracleConnection cn = new OracleConnection(_dsn))
            {
                PrepareCommand(cn, cmd, null, CommandType.Text, Sqlstr, parms);
                object val = cmd.ExecuteScalar();
                cmd.Parameters.Clear();
                return val;
            }
        }

        #endregion

        /// <summary>
        /// 执行SQL语句之前的准备工作
        /// </summary>
        /// <param name="cn">Connection</param>
        /// <param name="cmd">Command</param>
        /// <param name="tsn">Trasaction</param>
        /// <param name="cmdType">Command类型</param>
        /// <param name="cmdText">Command字符串 SQL语句</param>
        /// <param name="cmdParms">参数列表</param>
        protected void PrepareCommand(OracleConnection cn, OracleCommand cmd, OracleTransaction tsn, CommandType cmdType, string cmdText, OracleParameter[] cmdParms)
        {
            if (cn.State != ConnectionState.Open)
            {
                cn.Open();
            }

            cmd.Connection = cn;
            cmd.CommandText = cmdText;

            if (tsn != null)
            {
                cmd.Transaction = tsn;
            }

            cmd.CommandType = cmdType;

            if (cmdParms != null)
            {
                cmd.Parameters.AddRange(cmdParms);
            }
        }

        #region     "Disposable成员"
        private void myDispose()
        {
            Disponse(true);
            GC.SuppressFinalize(this);
        }
        #endregion

    }
}
